﻿-- =============================================
-- Author:		<Author,徐福龙>
-- Create date: <Create Date,2012 03 07>
-- Description:	<Description,根据页面URL查询出被使用的角色信息，仅应用于ADMIN账号>
-- =============================================
CREATE PROCEDURE [dbo].[proc_App_PageSearchRoles] 
	(
	@Search nvarchar(100),
	@SearchType int
	)
AS
BEGIN
	SET NOCOUNT ON;
IF @SearchType=1
BEGIN 
SELECT [ID]
		,ISNULL([MID],'0') as MID
      ,(ISNULL([MPath],'MPath')+' ['+ISNULL([MID],'MID')+','+ISNULL([MName],'MName')+']') AS TEXT
  FROM [App_Menu]
ORDER BY [MPath]	
END

IF @SearchType=2
BEGIN 
SELECT 
		ROW_NUMBER() OVER (ORDER BY [FAdminFlg] )AS Row
		,@Search as [Mid]
		,(select M.MName from [App_Menu] M where M.MID=@Search) as [MName]
		,(select M.MPath from [App_Menu] M where M.MID=@Search) as [MPath]
		--,R.MID AS RMID
		,R.[RName]
		,R.[RNote]
		,R.[FAdminFlg]
,Id
	FROM [App_Roles] R
	WHERE R.MID LIKE '%[[]'+@Search+']%'
END

IF @SearchType=3
BEGIN 

SELECT 
		ROW_NUMBER() OVER (ORDER BY M.[Mid],R.[RName] )AS Row
		--0 as row
		,M.[Mid]
		,M.[MName]
		,M.[MPath]
		--,R.MID AS RMID
		,R.[RName]
		,R.[RNote]
		,R.[FAdminFlg]
,R.Id
		FROM [App_Menu] M,[App_Roles] R
		WHERE ( M.MPath like '%'+@Search+'%' or  M.MName like '%'+@Search+'%' ) and R.MID LIKE '%[[]'+M.[Mid]+']%'
END

	SET NOCOUNT OFF;
END